package com.cndw.translate;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;

public class UpdateDB {

	/**
	 * 文件路径
	 */
	public static String filePath = "E:\\test\\excelOutput\\kungfu_qq-kfsys_monster-07-04.xls";

	public static void main(String[] args) {

		Workbook wookbook = null;
		InputStream is = null;
		try {
			// 创建对Excel工作簿文件的引用­
			String table = "";
			is = new FileInputStream(filePath);
			wookbook = new HSSFWorkbook(is);

			int sheets = wookbook.getNumberOfSheets();
			
			for (int sn = 0; sn < sheets; sn++) {

				HSSFSheet sheet = (HSSFSheet) wookbook.getSheetAt(sn);
				table = sheet.getSheetName();
				//取得字段名
				String[] columns = getValues(sheet.getRow(0)).split(",");
				
				String update = "UPDATE `" + table + "` SET ";
				
				// 获取到Excel文件中的所有行数
				int rows = sheet.getPhysicalNumberOfRows();
				// 遍历行
				for (int i = 1; i < rows; i++) {
					HSSFRow row = sheet.getRow(i);
					StringBuffer valueClause = new StringBuffer();
					
					if (row != null) {
						int id = 0;
						// 遍历列
						for (int j = 0; j < columns.length; j++) {
							// 获取到列的值
							HSSFCell cell = row.getCell(j);

							if (cell != null) {
								switch (cell.getCellType()) {
								case HSSFCell.CELL_TYPE_FORMULA:
									break;
								case HSSFCell.CELL_TYPE_NUMERIC:
									if(j == 0) {
										id = (int)cell.getNumericCellValue();
									}else {
										valueClause.append(columns[j]).append("=").append((int) cell.getNumericCellValue());
										valueClause.append(", ");
									}

									break;
								case HSSFCell.CELL_TYPE_STRING:
									String value = cell.getStringCellValue();
									if (j == 0) { 
										id = Integer.parseInt(value);
									} else {
//										String[] strArr1 = value.split("\r");
//										String[] strArr2 = value.split("\n");
										value = value.replace("'", "\"");
										value = value.replace("\n", "\r\n");
										value = value.equals("NULL") ? "" : value;
										valueClause.append(columns[j]).append("='").append(value).append("'");
										valueClause.append(", ");
									}
									break;
								case HSSFCell.CELL_TYPE_BLANK:
//									valueClause.append(columns[j]).append("='").append("'");
									break;
								default:

									break;
								}
							}
						}
						
						if (valueClause.toString().equals("")) {
							continue;
						}
						
						String where = " WHERE " + columns[0] + "=" + id;
						
						String excuteSql = update + valueClause.substring(0, valueClause.length() - 2) + where;
						
//						System.out.println(excuteSql);
						
//						JDBC.excute(excuteSql);
						
					}
				}
			}
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if(is != null) {
					is.close();
				}
				JDBC.close();
			} catch (Exception e2) {
			}
		}

	}


	public static String getValues(HSSFRow columnRow) {
		StringBuffer columns = new StringBuffer();
		int cells = columnRow.getPhysicalNumberOfCells();
		for (int i = 0; i < cells; i++) {
			HSSFCell cell = columnRow.getCell(i);

			if (cell != null) {
				switch (cell.getCellType()) {
				case HSSFCell.CELL_TYPE_FORMULA:
					break;
				case HSSFCell.CELL_TYPE_NUMERIC:
					columns.append("`").append((int) cell.getNumericCellValue())
							.append("`").append(", ");
					break;
				case HSSFCell.CELL_TYPE_STRING:
					columns.append("`").append(cell.getStringCellValue())
							.append("`").append(", ");
					break;
				case HSSFCell.CELL_TYPE_BLANK:

					break;
				default:

					break;
				}
			}
		}
		return columns.substring(0, columns.length() - 1);
	}

}
